package database.good;

import database.CRUD;
import database.GUImanager;
import database.employee.EmployeeCURDGUI;
import database.employee.tb_employee;
import redis.clients.jedis.Jedis;

import javax.swing.*;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.*;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

import static javax.swing.WindowConstants.DISPOSE_ON_CLOSE;

public class GoodCURDGUI  {
    private DefaultTableModel model;
    private JTable table;
    JFrame framegood;
    public GoodCURDGUI(tb_employee e1) throws SQLException {
        framegood=new JFrame();
        DefaultTableModel model;
        table = new JTable();
        table.setRowSelectionAllowed(true); // 允许通过点击选择行
        ListSelectionModel selectionModel = table.getSelectionModel();
        selectionModel.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); // 或者使用多选 ListSelectionModel.MULTIPLE_INTERVAL_SELECTION
        DefaultTableModel tableModel;
        int selectedRow; // 用于记录选中的行

        // 数据库连接信息
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "123456");
        Statement stmt = conn.createStatement();
        String query = "SELECT * FROM " + "tb_good";
        ResultSet rs = stmt.executeQuery(query);
        framegood.setTitle("超市管理系统");
        framegood.setBounds(100,100,800,600); // 设置窗口大小
        framegood.setDefaultCloseOperation(DISPOSE_ON_CLOSE); // 设置默认关闭操作
        framegood.setLocationRelativeTo(null); // 居中显示


        // Get metadata from ResultSet
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        String[] columnNames = new String[columnCount];
        for (int i = 0; i < columnCount; i++) {
            columnNames[i] = metaData.getColumnName(i + 1); // Columns in ResultSet start from index 1
        }

        // Create table model with columns from metadata
        model = new DefaultTableModel(columnNames, 0); // 0 rows initially
        table.getModel().addTableModelListener(new TableModelListener() {
            @Override
            public void tableChanged(TableModelEvent e) {
                if (e.getType() == TableModelEvent.UPDATE && e.getColumn() != -1) {
                    int rowIndex = e.getFirstRow();
                    int columnIndex = e.getColumn();
                    Object value = model.getValueAt(rowIndex, columnIndex);
                    // 检查单元格是否是新值，且不是主键列
                    if (rowIndex == table.getSelectedRow()) {
                        updateSelectedRow();
                    }
                }
            }
        });
        // Populate table model with data from ResultSet
        while (rs.next()) {
            Object[] rowData = new Object[columnCount];
            for (int i = 0; i < columnCount; i++) {
                rowData[i] = rs.getObject(i + 1); // ResultSet columns start from index 1
            }
            model.addRow(rowData);
        }

        // Create JTable with the model
        table = new JTable(model);

        // Create a scroll pane for the table
        JScrollPane scrollPane = new JScrollPane(table);
        framegood.add(scrollPane, BorderLayout.CENTER);

        // Panel for buttons
        JPanel buttonPanel = new JPanel();
        JButton addButton = new JButton("添加");
        addButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    openAddDialog(e1);
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }
            }
        });
        buttonPanel.add(addButton);

        JButton deleteButton = new JButton("删除");
        deleteButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                deleteSelectedRows();
            }
        });
        buttonPanel.add(deleteButton);

        JButton updateButton = new JButton("更新");
        updateButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                updateSelectedRow();
            }
        });
        buttonPanel.add(updateButton);
        JButton ExportButton = new JButton("导出");
        ExportButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                exportSelectedRowToCsv(table);
            }
        });
        buttonPanel.add(ExportButton);
        JButton returnButton =new JButton("返回");
        returnButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                new GUImanager(e1);
                framegood.dispose();
            }
        });
        buttonPanel.add(returnButton);
        framegood.add(buttonPanel, BorderLayout.SOUTH);

        framegood.setVisible(true);
    }

    private void openAddDialog(tb_employee e1) throws SQLException {
        // 关闭任何打开的框架
        framegood.dispose();

        // 创建新的JFrame用于添加商品
        JFrame addFrame = new JFrame("新增商品页面");
        addFrame.setSize(800, 600);
        addFrame.setDefaultCloseOperation(DISPOSE_ON_CLOSE);
        addFrame.setLocationRelativeTo(null); // 居中显示
        // 使用BorderLayout布局管理器的JPanel
        JPanel addPanel = new JPanel(new BorderLayout());

        // 假设CRUD类处理数据库操作
        CRUD crud = new CRUD();
        Connection conn = crud.getConnection();

        // 从tb_good表获取列名
        Statement stmt = conn.createStatement();
        String query = "SELECT * FROM tb_good LIMIT 1"; // 限制为1行以获取列名
        ResultSet rs = stmt.executeQuery(query);
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        // 列名数组
        String[] columnNames = new String[columnCount];
        for (int i = 0; i < columnCount; i++) {
            columnNames[i] = metaData.getColumnName(i + 1); // 列名从1开始计数
        }

        // 创建表格模型并添加列名
        DefaultTableModel addTableModel = new DefaultTableModel(columnNames, 0);

        // 创建JTable
        JTable addTable = new JTable(addTableModel);

        // 查询tb_customer表以填充JComboBox
        String customerQuery = "SELECT Cid FROM tb_customer";
        List<String> cidList = new ArrayList<>();
        try (Statement customerStmt = conn.createStatement();
             ResultSet customerRs = customerStmt.executeQuery(customerQuery)) {
            while (customerRs.next()) {
                cidList.add(customerRs.getString("Cid"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "加载客户编号失败: " + ex.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        }

        // 为Cid列创建JComboBox编辑器
        JComboBox<String> cidComboBox = new JComboBox<>(cidList.toArray(new String[0]));
        // 设置表格的列编辑器
        addTable.getColumnModel().getColumn(3).setCellEditor(new DefaultCellEditor(cidComboBox));

        // 将JTable添加到JScrollPane
        JScrollPane addScrollPane = new JScrollPane(addTable);
        addPanel.add(addScrollPane, BorderLayout.CENTER);

        // 新增按钮
        JButton addButton = new JButton("新增");
        addButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // 添加空白行
                addTableModel.addRow(new Object[addTableModel.getColumnCount()]);
            }
        });

        // 保存按钮
        JButton saveButton = new JButton("保存");
        saveButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                    try {
                        if (addTableModel.getRowCount() == 0) {
                            JOptionPane.showMessageDialog(addFrame, "表格为空，没有数据需要保存。", "提示", JOptionPane.INFORMATION_MESSAGE);
                            return; // 直接返回，不执行保存操作
                        }
                        // 构建并执行插入语句
                        StringBuilder sb = new StringBuilder();
                        sb.append("INSERT INTO tb_good (");
                        for (int j = 0; j < columnNames.length; j++) {
                            sb.append(columnNames[j]);
                            if (j < columnNames.length - 1) {
                                sb.append(", ");
                            }
                        }
                        sb.append(") VALUES (");
                        for (int j = 0; j < columnNames.length; j++) {
                            sb.append("? ");
                            if (j < columnNames.length - 1) {
                                sb.append(", ");
                            }
                        }
                        sb.append(")");

                        String insertQuery = sb.toString();
                        PreparedStatement insertStmt = conn.prepareStatement(insertQuery);

                        // 为每个表行设置值并执行插入
                        for (int i = 0; i < addTableModel.getRowCount(); i++) {
                            for (int j = 0; j < columnNames.length; j++) {
                                Object value = addTableModel.getValueAt(i, j);
                                // 根据值的类型设置PreparedStatement的参数
                                if (value instanceof String) {
                                    insertStmt.setString(j + 1, (String) value);
                                } else if (value instanceof Integer) {
                                    insertStmt.setInt(j + 1, (Integer) value);
                                } else if (value instanceof Float) {
                                    insertStmt.setFloat(j + 1, (Float) value);
                                } else {
                                    insertStmt.setObject(j + 1, value);
                                }
                            }
                            insertStmt.addBatch();
                        }
                        insertStmt.executeBatch();
                        JOptionPane.showMessageDialog(addFrame, "数据保存成功！");
                    } catch (SQLException ex) {
                        JOptionPane.showMessageDialog(addFrame, "数据保存失败：" + ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
                        ex.printStackTrace();
                    }
                }

        });

        // 返回按钮
        JButton returnButton = new JButton("返回");
        returnButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                addFrame.dispose();
                // 可能需要根据实际情况调整返回逻辑
                try {
                    new GoodCURDGUI(e1); // 假设这是返回主界面的方法
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }
            }
        });

        // 按钮面板
        JPanel buttonPanel = new JPanel();
        buttonPanel.add(addButton);
        buttonPanel.add(saveButton);
        buttonPanel.add(returnButton);

        // 将按钮面板添加到JPanel
        addPanel.add(buttonPanel, BorderLayout.SOUTH);

        // 将JPanel添加到JFrame
        addFrame.add(addPanel);
        addFrame.setVisible(true);

        // 关闭数据库Statement和ResultSet
        rs.close();
        stmt.close();
    }


    // Method to delete selected rows
    private void deleteSelectedRows() {
        // 获取实际的表格模型
        DefaultTableModel model = (DefaultTableModel) table.getModel();

        // 检查表格是否已初始化
        if (table == null) {
            JOptionPane.showMessageDialog(framegood, "表格未初始化。", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }

        // 获取选中的行
        int[] selectedRows = table.getSelectedRows();
        if (selectedRows.length == 0) {
            JOptionPane.showMessageDialog(framegood, "请选择要删除的商品。", "警告", JOptionPane.WARNING_MESSAGE);
            return;
        }

        // 遍历选中的行
        for (int i = selectedRows.length - 1; i >= 0; i--) {
            int rowIndex = table.convertRowIndexToModel(selectedRows[i]);
            // 确保 rowIndex 在有效范围内
            if (rowIndex < 0 || rowIndex >= model.getRowCount()) {
                continue; // 跳过无效的行
            }

            // 假设商品ID（Gid）存储在模型的第一列
            String Gid = (String) model.getValueAt(rowIndex, 0);

            // 检查采购明细表中是否有记录引用了该商品ID
            String checkSql = "SELECT COUNT(*) FROM tb_pay_detail WHERE Gid = ?";
            try (Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "123456");
                 PreparedStatement pstmt = conn.prepareStatement(checkSql)) {
                pstmt.setString(1, Gid);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next() && rs.getInt(1) > 0) {
                    // 如果采购明细表中有记录引用了该商品ID，提示用户是否删除
                    int confirm = JOptionPane.showConfirmDialog(framegood, "该商品有关联的采购明细，是否删除这些明细以及商品信息？", "确认删除",
                            JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
                    if (confirm == JOptionPane.YES_OPTION) {
                        // 如果用户确认，删除采购明细表中的记录
                        String deletePurchaseDetailsSql = "DELETE FROM tb_pay_detail WHERE Gid = ?";
                        try (PreparedStatement deletePurchaseDetailsStmt = conn.prepareStatement(deletePurchaseDetailsSql)) {
                            deletePurchaseDetailsStmt.setString(1, Gid);
                            int rowsAffected = deletePurchaseDetailsStmt.executeUpdate();
                            if (rowsAffected > 0) {
                                // 然后删除商品表中的商品记录
                                String deleteGoodsSql = "DELETE FROM tb_good WHERE Gid = ?";
                                try (PreparedStatement deleteGoodsStmt = conn.prepareStatement(deleteGoodsSql)) {
                                    deleteGoodsStmt.setString(1, Gid);
                                    rowsAffected = deleteGoodsStmt.executeUpdate();
                                    if (rowsAffected > 0) {
                                        model.removeRow(rowIndex); // 从表格模型中移除行
                                    } else {
                                        JOptionPane.showMessageDialog(framegood, "商品删除操作失败。", "错误", JOptionPane.ERROR_MESSAGE);
                                    }
                                }
                            }
                        }
                    }
                } else {
                    // 如果采购明细表中没有记录引用该商品ID，直接删除商品表中的商品记录
                    String deleteGoodsSql = "DELETE FROM tb_good WHERE Gid = ?";
                    try (PreparedStatement deleteGoodsStmt = conn.prepareStatement(deleteGoodsSql)) {
                        deleteGoodsStmt.setString(1, Gid);
                        int rowsAffected = deleteGoodsStmt.executeUpdate();
                        if (rowsAffected > 0) {
                            model.removeRow(rowIndex); // 从表格模型中移除行
                        } else {
                            JOptionPane.showMessageDialog(framegood, "商品删除操作失败。", "错误", JOptionPane.ERROR_MESSAGE);
                        }
                    }
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
                JOptionPane.showMessageDialog(framegood, "数据库错误: " + ex.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
            }
        }
    }
    // Method to update selected row
    private void updateSelectedRow() {
        int selectedRow = table.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(framegood, "Please select a row to update.", "Update Error", JOptionPane.ERROR_MESSAGE);
            return;
        }

        DefaultTableModel model = (DefaultTableModel) table.getModel();
        StringBuilder sql = new StringBuilder("UPDATE tb_good SET ");

        java.util.List<String> updateColumns = new ArrayList<>();//存储列名和对应占位符
        List<Object> params = new ArrayList<>();//存储与这些占位符对应的实际参数

        for (int i = 0; i < model.getColumnCount(); i++) {
            String columnName = model.getColumnName(i);//获取列名
            Object value = model.getValueAt(selectedRow, i);//获取模型中特定行 selectedRow 和列 i 的数据值。
            // 跳过主键列 "GID"
            if (!"Gid".equals(columnName)) {
                // 为更新语句添加列名
                updateColumns.add(columnName + " = ");
                // 存储参数值
                params.add(value);
            }
        }
        int i1=0;
        for(Object updateColumn:updateColumns) {
            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i1);
                sql.append(updateColumn);
                sql.append("'");
                sql.append(param);
                sql.append("'");
                if(i1 != (updateColumns.size()-1)){
                    sql.append(",");
                }
                i1++;
                break;
            }
        }

        // 添加主键条件
        sql.append(" WHERE GID = ");
        sql.append("'");
        sql.append(model.getValueAt(selectedRow, 0));
        sql.append("'");
        // 主键值也作为参数传入

        System.out.println(sql.toString());
        // 执行数据库更新操作
        String finalSql = sql.toString();
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "123456");
             PreparedStatement pstmt = conn.prepareStatement(finalSql)) {

            // 执行更新操作
            int rowsUpdated = pstmt.executeUpdate();
            if (rowsUpdated > 0) {
                JOptionPane.showMessageDialog(framegood, "Row updated successfully.", "Update Successful", JOptionPane.INFORMATION_MESSAGE);
            } else {
                JOptionPane.showMessageDialog(framegood, "Failed to update row. No rows affected.", "Update Failed", JOptionPane.ERROR_MESSAGE);
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(framegood, "SQL Error: " + e.getMessage(), "Update Failed", JOptionPane.ERROR_MESSAGE);
            e.printStackTrace();
        }
    }

    public void RedisCopy() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "123456");Statement stmt = conn.createStatement();
        String query = "SELECT * FROM " + "tb_good";
        ResultSet rs = stmt.executeQuery(query);
        ResultSetMetaData metaData = rs.getMetaData();
        String listKey = "tb_good"; // 表名作为列表的键
        while (rs.next()) {
            List<String> rowList = new ArrayList<>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                rowList.add(rs.getString(i));
            }
            String rowKey = listKey + ":" + rs.getInt("Gid"); // 使用Gid作为列表中元素的键
            Jedis redisClient = new Jedis("localhost", 6379);
            for( String item : rowList)
            {
                redisClient.rpush(rowKey, item);
            }

            redisClient.close();
        }
    }
    public void exportSelectedRowToCsv(JTable table) {
        int selectedRow = table.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(null, "请选择一个要导出的行。", "提示", JOptionPane.WARNING_MESSAGE);
            return;
        }
        LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String formattedDateTime = now.format(formatter);
        // 调用JFileChooser选择文件路径
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("选择CSV文件存放路径");
        int result = fileChooser.showSaveDialog(null);
        if (result == JFileChooser.APPROVE_OPTION) {
            File selectedFile = fileChooser.getSelectedFile();
            String csvFilePath = selectedFile.getAbsolutePath();

            // 确保文件名以.csv结尾
            if (!csvFilePath.endsWith(".csv")) {
                csvFilePath += ".csv";
            }

            try (OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(csvFilePath), "UTF-8");
                 PrintWriter pw = new PrintWriter(osw)) {

                TableModel model = table.getModel();
                int columnCount = model.getColumnCount();

                // 写入CSV文件数据
                for (int column = 0; column < columnCount; column++) {
                    Object value = model.getValueAt(selectedRow, column);
                    // 将值转换为字符串，并确保字符串编码为UTF-8
                    String stringValue = (value == null) ? "" : value.toString();
                    pw.print(stringValue);
                    if (column < columnCount - 1) {
                        pw.print(",");
                    }
                }
                pw.println();

                JOptionPane.showMessageDialog(null, "数据导出成功，文件已保存到：" + csvFilePath);
            } catch (IOException e) {
                JOptionPane.showMessageDialog(null, "导出失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
                e.printStackTrace();
            }
        } else {
            System.out.println("文件保存操作被取消");
        }
    }
    /*public static void main(String[] args) {
        // Example usage:
        SwingUtilities.invokeLater(() -> {
            try {
                GoodCURDGUI d1 = new GoodCURDGUI();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
    }*/
}

